package com.xunshibao.core.dao.impl;

import java.io.Serializable;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.jdbc.Work;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.TransactionCallback;

import com.xunshibao.core.dao.BaseDao;
import com.xunshibao.core.dao.ExecuteHql;
import com.xunshibao.core.dao.TransactionItem;
import com.xunshibao.core.dao.page.Pagination;

public class BaseDaoImpl extends GenericDaoImpl implements BaseDao {
	final String EXCEPTIONNAME = "Dao Exception at SpringTemplateDaoImpl:";
	private static final Log LOG = LogFactory.getLog(BaseDaoImpl.class);

	public static final String ROW_COUNT = "select count(*) ";
	public static final String FROM = "from";
	public static final String HQL_FETCH = "fetch";
	public static final String ORDER_BY = "order";

	public BaseDaoImpl() {
		super();
	}

	@Override
	public List<?> find(String hql, Map<String, Object> params) {
		if (params != null) {
			int size = params.size();
			String[] paramNames = new String[size];
			Object[] values = new Object[size];
			int i = 0;
			for (Map.Entry<String, Object> entry : params.entrySet()) {
				paramNames[i] = entry.getKey();
				values[i] = entry.getValue();
				i++;
			}
			return this.hibernateTemplate.findByNamedParam(hql, paramNames, values);
		} else {
			return this.hibernateTemplate.find(hql);
		}
	}

	@Override
	public List<?> findNamed(String queryName, Map<String, Object> params) {
		if (params != null) {
			int size = params.size();
			String[] paramNames = new String[size];
			Object[] values = new Object[size];
			int i = 0;
			for (Map.Entry<String, Object> entry : params.entrySet()) {
				paramNames[i] = entry.getKey();
				values[i] = entry.getValue();
				i++;
			}
			return this.hibernateTemplate.findByNamedQueryAndNamedParam(queryName, paramNames, values);
		} else {
			return this.hibernateTemplate.findByNamedQuery(queryName);
		}
	}

	@Override
	public Object findSingle(String hql, Map<String, Object> params) {
		return findAllSingle(hql, params, false);
	}

	/**
	 * 
	 * @param hql
	 *            命名查询名称或hql语句
	 * @param params
	 * @param isNamed
	 *            true使用命名查询，false使用hql查询
	 * @return
	 */
	private Object findAllSingle(String hql, Map<String, Object> params, boolean isNamed) {
		List resultList = null;
		if (params != null) {
			int size = params.size();
			String[] paramNames = new String[size];
			Object[] values = new Object[size];
			int i = 0;
			for (Map.Entry<String, Object> entry : params.entrySet()) {
				paramNames[i] = entry.getKey();
				values[i] = entry.getValue();
				i++;
			}

			if (isNamed) {// 使用命名查询
				resultList = this.hibernateTemplate.findByNamedQueryAndNamedParam(hql, paramNames, values);
			} else { // 使用hql查询
				resultList = this.hibernateTemplate.findByNamedParam(hql, paramNames, values);
			}
		} else {
			if (isNamed) { // 使用命名查询
				resultList = this.hibernateTemplate.findByNamedQuery(hql);
			} else {// 使用hql查询
				resultList = this.hibernateTemplate.find(hql);
			}
		}

		try {
			if (resultList != null && resultList.size() == 1) {
				return resultList.get(0);
			}
			return null;
		} catch (Exception e) {
			LOG.error(EXCEPTIONNAME, e);
			return null;
		}
	}

	@Override
	public Long getRecordCount(String hql) {
		return getRecordCount(hql, null);
	}

	@Override
	public Long getRecordCount(String hql, Map<String, Object> params) {
		try {
			Object obj = findSingle(hql, params);
			return (Long) obj;
		} catch (Exception e) {
			LOG.error(EXCEPTIONNAME, e);
			return null;
		}
	}

	/**
	 * 生成count形式的hql语句
	 * 
	 * @param hql
	 *            hql语句
	 * @return count hql语句
	 */
	protected String getRowCountHql(String hql) {
		int fromIndex = hql.toLowerCase().indexOf(FROM);
		hql = hql.substring(fromIndex);
		String rowCountHql = hql.replace(HQL_FETCH, "");

		int index = rowCountHql.indexOf(ORDER_BY);
		if (index > 0) {
			rowCountHql = rowCountHql.substring(0, index);
		}
		return ROW_COUNT + rowCountHql;
	}

	@Override
	public int hqlUpdate(final String hql, final Map<String, Object> params) {
		return hqlAllUpdate(hql, params, false);
	}

	private int hqlAllUpdate(final String hql, final Map<String, Object> params, final boolean isNamed) {
		return hibernateTemplate.execute(new HibernateCallback<Integer>() {
			@Override
			public Integer doInHibernate(Session session) throws HibernateException, SQLException {
				Query query = null;
				if (isNamed) {
					query = session.getNamedQuery(hql);
				} else {
					query = session.createQuery(hql);
				}
				if (params != null) {
					applyNamedParameterToQuery(query, params);
				}
				try {
					int result = query.executeUpdate();
					return result;
				} catch (Exception e) {
					return -1;
				}

			}
		});
	}

	@Override
	public int hqlUpdate(String hql) {
		return hqlUpdate(hql, null);
	}

	@Override
	public Pagination pageFind(String hql, int pageNo, int pageSize) {
		return pageFind(hql, pageNo, pageSize, null);
	}

	@SuppressWarnings("unchecked")
	@Override
	public Pagination pageFind(final String hql, final int pageNo, final int pageSize, final Map<String, Object> params) {
		int totalCount = countQueryResult(hql, params);
		final Pagination p = new Pagination(pageNo, pageSize, totalCount);
		if (totalCount < 1) {
			p.setList(Collections.emptyList());
			return p;
		}
		List polist = null;
		try {
			polist = this.hibernateTemplate.executeFind(new HibernateCallback<List>() {
				@Override
				public List doInHibernate(Session session) throws HibernateException, SQLException {
					Query query = session.createQuery(hql);
					if (params != null) {
						applyNamedParameterToQuery(query, params);
					}
					query.setFirstResult(p.getFirstResult());
					query.setMaxResults(pageSize);
					return query.list();
				}
			});
		} catch (Exception e) {
			LOG.error(EXCEPTIONNAME, e);
			polist = Collections.emptyList();
		}

		p.setList(polist);

		return p;
	}

	/**
	 * 执行count语句求记录数
	 * 
	 * @param hql
	 *            count hql语句
	 * @param params
	 * @return 记录总数
	 */
	protected int countQueryResult(String hql, Map<String, Object> params) {
		hql = this.getRowCountHql(hql);
		Long count = this.getRecordCount(hql, params);
		if (count != null) {
			return count.intValue();
		} else {
			return 0;
		}
	}

	@Override
	public Pagination pageNamedFind(String queryName, int pageNo, int pageSize) {
		return pageNamedFind(queryName, pageNo, pageSize);
	}

	@Override
	public Pagination pageNamedFind(final String queryName, final int pageNo, final int pageSize, final Map<String, Object> params) {
		return hibernateTemplate.execute(new HibernateCallback<Pagination>() {
			@Override
			public Pagination doInHibernate(Session session) throws HibernateException, SQLException {
				Query query = session.getNamedQuery(queryName);
				String hql = query.getQueryString();
				return pageFind(hql, pageNo, pageSize, params);
			}
		});
	}

	@Override
	public Object findNamedSingle(String queryName, Map<String, Object> params) {
		return findAllSingle(queryName, params, true);
	}

	@Override
	public Long getNamedRecordCount(String queryName) {
		return getNamedRecordCount(queryName, null);
	}

	@Override
	public Long getNamedRecordCount(String queryName, Map<String, Object> params) {
		try {
			Object count = findNamedSingle(queryName, params);
			return (Long) count;
		} catch (Exception e) {
			LOG.error(EXCEPTIONNAME, e);
			return null;
		}
	}

	/**
	 * Apply the given name parameter to the given Query object.
	 * 
	 * @param queryObject
	 *            the Query object
	 * @param params
	 *            the name and value of the parameter, in the form of
	 *            {name:value}
	 * @throws HibernateException
	 *             if thrown by the Query object
	 */
	@SuppressWarnings("unchecked")
	protected void applyNamedParameterToQuery(Query queryObject, Map<String, Object> params) throws HibernateException {
		if (params != null) {
			Object value;
			String paramName;
			for (Map.Entry<String, Object> entry : params.entrySet()) {
				value = entry.getValue();
				paramName = entry.getKey();
				if (value instanceof Collection) {
					queryObject.setParameterList(paramName, (Collection) value);
				} else if (value instanceof Object[]) {
					queryObject.setParameterList(paramName, (Object[]) value);
				} else {
					queryObject.setParameter(paramName, value);
				}
			}
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public void execDDL(final String ddl) {
		this.hibernateTemplate.executeWithNativeSession(new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session) throws HibernateException, SQLException {
				session.doWork(new Work() {
					@Override
					public void execute(Connection connection) throws SQLException {
						PreparedStatement pstmt = connection.prepareStatement(ddl);
						pstmt.execute();
					}
				});
				return null;
			}
		});
	}

	@Override
	public List<Map<String, Object>> findSQL(final String sql, final Map<String, Object> params) {
		return this.findSQL(sql, params, null, null);
	}

	/**
	 * sql = select s as a, b as b, c.c as c from c b, e f
	 * 
	 * @param sql
	 * @return 列名
	 */
	private List<String> parseSQL(String sql) {
		List<String> columns = new ArrayList<String>();
		int sidx = sql.indexOf("select") + 6; // 6 = "select".length();
		int fidx = sql.indexOf("from");
		sql = sql.substring(sidx, fidx);
		String[] cols = sql.split(",");
		for (String c : cols) {
			if (c.indexOf(" as ") != -1) {
				columns.add(c.split(" as ")[1].trim());
			} else {
				columns.add(c.trim());
			}
		}
		return columns;
	}

	public static void main(String[] args) {
		BaseDaoImpl b = new BaseDaoImpl();
		b.parseSQL("select s as a, b as b, c.c as c from c b, e f ");
	}

	@Override
	public int hqlNamedUpdate(String queryName, Map<String, Object> params) {
		return hqlAllUpdate(queryName, params, true);
	}

	@Override
	public int hqlNamedUpdate(String queryName) {
		return hqlNamedUpdate(queryName, null);
	}

	@Override
	public Integer getSeqNextVal(final String sequenceName) {

		return this.hibernateTemplate.execute(new HibernateCallback<Integer>() {

			@Override
			public Integer doInHibernate(Session session) throws HibernateException, SQLException {
				String nativeSQL = "select " + sequenceName + ".nextval from dual";
				SQLQuery query = session.createSQLQuery(nativeSQL);
				return ((BigDecimal) query.uniqueResult()).intValue();
			}
		});
	}

	@Override
	public List<Map<String, Object>> findSQL(String sql) {
		return this.findSQL(sql, null);
	}

	@Override
	public Map<String, Object> findSQLSingle(String sql, Map<String, Object> params) {
		List<Map<String, Object>> results = this.findSQL(sql, params);
		if (results.isEmpty()) {
			return null;
		} else {
			return results.get(0);
		}
	}

	@Override
	public Map<String, Object> findSQLSingle(String sql) {
		return this.findSQLSingle(sql, null);
	}

	@Override
	public int sqlUpdate(final String sql, final List<Object> params) {

		return this.hibernateTemplate.executeWithNativeSession(new HibernateCallback<Integer>() {
			List<Integer> cntList = new ArrayList<Integer>(1);

			@Override
			public Integer doInHibernate(Session session) throws HibernateException, SQLException {
				session.doWork(new Work() {
					@Override
					public void execute(Connection connection) throws SQLException {
						PreparedStatement pstmt = connection.prepareStatement(sql);

						// 设置查询参数
						if (params != null) {
							int i = 1;
							for (Object pvalue : params) {
								pstmt.setObject(i, pvalue);
								i++;
							}
						}
						cntList.add(pstmt.executeUpdate());
					}
				});
				if (cntList.isEmpty()) {
					return 0;
				} else {
					return cntList.get(0);
				}
			}
		});
	}

	@Override
	public int sqlUpdate(String sql) {
		return sqlUpdate(sql, null);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Map<String, Object>> findSQL(final String sql, final Map<String, Object> params, final Integer startIndex, final Integer pageSize) {
		return this.hibernateTemplate.executeFind(new HibernateCallback<List<Map<String, Object>>>() {

			@Override
			public List<Map<String, Object>> doInHibernate(Session session) throws HibernateException, SQLException {
				List<Map<String, Object>> rows = Collections.emptyList();
				List<String> cols = parseSQL(sql);
				SQLQuery query = session.createSQLQuery(sql);
				applyNamedParameterToQuery(query, params);

				// 设置分页参数
				if (startIndex != null && pageSize != null && pageSize != 0) {
					query.setFirstResult(startIndex);
					query.setMaxResults(pageSize);
				}

				List result = query.list();
				if (result != null && !result.isEmpty()) {
					int size = result.size();
					int colsLen = cols.size();
					rows = new ArrayList<Map<String, Object>>(size);
					for (int i = 0; i < size; i++) {
						Map<String, Object> row = new LinkedHashMap<String, Object>();
						if (colsLen > 1) {
							Object[] objs = (Object[]) result.get(i);
							for (int j = 0; j < colsLen; j++) {
								row.put(cols.get(j), objs[j]);
							}
						} else {
							Object obj = result.get(i);
							row.put(cols.get(0), obj);
						}
						rows.add(row);
					}
				}
				return rows;
			}
		});
	}

	@Override
	public List<Map<String, Object>> findSQL(String sql, Integer startIndex, Integer pageSize) {
		return this.findSQL(sql, null, startIndex, pageSize);
	}

	/**
	 * Get entity by PK
	 * 
	 * @param pk
	 *            PK
	 * @param entityClass
	 *            Entity class
	 * @return Entity
	 */
	protected Object get(Serializable pk, Class entityClass) {
		return hibernateTemplate.get(entityClass, pk);
	}

	@Override
	public int executeTransaction(final List<ExecuteHql> lstExecuteHqls) {
		return this.transactionTemplate.execute(new TransactionCallback<Integer>() {
			@Override
			public Integer doInTransaction(TransactionStatus status) {
				int effectRows = 0;
				for (ExecuteHql hql : lstExecuteHqls) {
					try {
						effectRows += hqlAllUpdate(hql.getHql(), hql.getParam(), false);
					} catch (Exception ex) {
						LOG.error(ex.getMessage(), ex);
						status.setRollbackOnly();
					}
				}
				return effectRows;
			}
		});
	}

	@Override
	public int execTransaction(final List<TransactionItem> lstExecuteHqls) {
		return this.transactionTemplate.execute(new TransactionCallback<Integer>() {
			@Override
			public Integer doInTransaction(TransactionStatus status) {
				int effectRows = 0;
				for (TransactionItem hql : lstExecuteHqls) {
					try {
						if (hql.getType() == TransactionItem.ENTITY) {
							hibernateTemplate.save(hql.getEntity());
							effectRows++;
						} else if (hql.getType() == TransactionItem.EHQL) {
							effectRows += hqlAllUpdate(hql.getHql().getHql(), hql.getHql().getParam(), false);
						}
					} catch (Exception ex) {
						LOG.error(ex.getMessage(), ex);
						status.setRollbackOnly();
					}
				}
				return effectRows;
			}
		});
	}

}